package com.tt.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.smartcardio.CardException;

import com.sun.jndi.cosnaming.CNNameParser;
import com.sun.media.jfxmedia.control.VideoDataBuffer;
import com.sun.org.apache.bcel.internal.generic.Select;
import com.sun.org.apache.regexp.internal.recompile;
import com.sun.org.glassfish.gmbal.Description;
import com.sun.xml.internal.bind.v2.model.core.ID;
import com.tt.Connection.DB;
import com.tt.shpping.Category;
import com.tt.shpping.User;

public class CategoryDAO {
	
	public static void save(Category c) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn = DB.getConnection();
		String sql = "insert into category values (null, ?, ?, ?, ?, ?)";
		pstmt = DB.getPstmt(conn, sql);
		System.out.println(pstmt);
		try {
			pstmt.setString(1, c.getName());
			pstmt.setString(2, c.getDescr());
			pstmt.setInt(3, c.getPid());
			pstmt.setInt(4, c.isLeaf() ? 0 : 1);
			pstmt.setInt(5, c.getGrade());
			pstmt.executeUpdate();  
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DB.closPstmt(pstmt);
			DB.closeConnection(conn);
		}	
	}
	
	public static void addTopCategory(String name, String descr){
		Category c = new Category();
		c.setName(name);
		c.setDescr(descr);
		c.setPid(0);
		c.setLeaf(true);
		c.setGrade(1);
		CategoryDAO.save(c);
		System.out.println(1);
	}
	
	public static int getGrade(int id) {
		Connection conn = null;
		ResultSet rs = null;
		conn = DB.getConnection();
		int grade = 0;
		String sql = "select * from category where id = " +id;
		rs = DB.getResultSet(conn, sql);
		try {
			if(rs.next()) {
				grade = rs.getInt("grade");
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return grade;
 	}
	public static void addChildCategory(String name, String descr, int pid){
		Connection conn = null;
		PreparedStatement pstmt = null;
		Statement stmt = null;
		conn = DB.getConnection();
		try {
			conn.setAutoCommit(false);
			int grade = getGrade(pid);
			String sql = "insert into category values (null, ?, ?, ?, ?, ?)";
			pstmt = DB.getPstmt(conn, sql);
			System.out.println(pstmt);
			pstmt.setString(1, name);
			pstmt.setString(2, descr);
			pstmt.setInt(3, pid);
			pstmt.setInt(4, 0);
			pstmt.setInt(5, grade+1);
			pstmt.executeUpdate();
			String sql2 = "update category set isleaf = 1 where id = "+pid;
			stmt = DB.getStmt(conn);
			stmt.executeUpdate(sql2);
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			DB.closeStmt(stmt);
			DB.closPstmt(pstmt);
			DB.closeConnection(conn);
		}	
	}
	
	public static void daleteCategory(int id) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		conn = DB.getConnection();
		stmt = DB.getStmt(conn);
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		int pid = getParentID(id);
		System.out.println(pid);
		deleteC(conn, id);
		try {
			if(pid!=0) {
				String sql = "select count(*) from category where pid ="+  pid;
				rs = DB.getResultSet(conn, sql);	
				rs.next();
				int count = rs.getInt(1);
				if(count<=0) {
					stmt.executeUpdate("update category set isleaf = 0 where id = "+pid);
				}	
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DB.closeRs(rs);
			DB.closeStmt(stmt);
			DB.closeConnection(conn);
		}
		
	}
	public static void deleteC( Connection conn, int id) {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = DB.getStmt(conn);
			String sql = "select * from category where pid = "+id;
			rs = DB.getResultSet(conn, sql);
			while(rs.next()) {
				int pid = rs.getInt("id");
				deleteC(conn,pid);
			}
			stmt.executeUpdate("delete from category where id ="+id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DB.closeRs(rs);
			DB.closeStmt(stmt);
		}
 	}
	
	public static int getParentID(int id) {
		Connection conn = null;
		ResultSet rs = null;
		int pi = 0;
		conn = DB.getConnection();
		String sql = "select * from category where id = "+id;
		rs = DB.getResultSet(conn, sql);
		try {
			if(rs.next()){
				pi = rs.getInt("pid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pi;
	}
	public static void  getCategories(List<Category> list, int id ){
		Connection conn = null;
		ResultSet rs = null;	
		String sql = "select * from category where pid = " +id;
		conn = DB.getConnection();
		rs = DB.getResultSet(conn, sql);
		try {
			while(rs.next()) {
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setPid(rs.getInt("pid"));
				c.setLeaf(rs.getInt("isleaf")==0? true:false);
				c.setGrade(rs.getInt("grade"));
				list.add(c);
				if(!c.isLeaf()){
					getCategories(list, c.getId());
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				

		
		
		
	}
	
	public  static List<Category> getCategories(){
		List<Category> list = new ArrayList<Category>();
		getCategories(list,0);
		return list;
	}
	  
	  
}
